import sqlite3
from sqlite3 import OperationalError


def create_sqlite():
    conn = sqlite3.connect('./test.sqlite')
    cur = conn.cursor()
    try:
        sql = """CREATE TABLE test (
                    id integer primary key autoincrement,
                    name varchar(15) not null,
                    hight float,
                    weight float
                );"""
        cur.execute(sql)
        print("create table success")
        return True
    except OperationalError as o:
        print(str(o))
        pass
        if str(o) == "table insert already exists":
            return True
        return False
    except Exception as e:
        print(e)
        return False
    finally:
        cur.close()
        conn.close()


def insert():
    conn = sqlite3.connect('./test.sqlite')
    cur = conn.cursor()
    try:
        # 插入单条语句
        insert_sql = """insert into test(name,hight,weight) values('kfx',167,55.1);"""
        cur.execute(insert_sql)
        print("rowcount1:", cur.rowcount)  # 结果：1

        # 插入多条语句，注意sqlite使用?做占位符
        insert_many_sql = """insert into test(name,hight,weight) values(?,?,?);"""
        data_list = [("kql", 175, 70), ("zcy", 165, 70)]
        cur.executemany(insert_many_sql, data_list)
        print("rowcount2:", cur.rowcount)  # 结果：2

        # 更新操作
        cur.execute("""update test set name="kqq" where id=1; """)
        print("rowcount3:", cur.rowcount)  # 结果：1

        # 删除操作
        cur.execute("select * from test where id=2;")

        # 查询
        cur.execute("select * from test where id > 2;")
        print(cur.fetchone())  # 取出第一个，返回元组或None，例如(3, 'zcy', 165.0, 70.0)
        print(cur.fetchone())  # 取出第二个，每取一个游标里就少一个(4, 'kfx', 167.0, 55.1)
        print(
            cur.fetchall())  # 返回一个列表套元组或空列表，例如[(5, 'kql', 175.0, 70.0), (6, 'zcy', 165.0, 70.0)]

        conn.commit()
    except Exception as e:
        print(str(e))
    finally:
        cur.close()
        conn.close()


if __name__ == '__main__':
    # create_sqlite()
    insert()
